"""
Case Type   : 系统内部使用工具
Case Name   : 分区表创建本地索引
Create At   : 2022/03/03
Owner       : zou_jialiang050
Description :
    1.构建数据
    2.清理环境
Expect      :
    1.构建成功
    2. 成功
History     :
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.Common import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Constant = Constant()


class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pricomsh = CommonSH("PrimaryDbUser")
        self.primary_node = Node('PrimaryDbUser')
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "source_data")

    def test_ustore(self):
        text = f"----step1:分区表创建本地索引:expect:成功;----"
        self.log.info(text)
        file_lis = ['tab_ap_join_04.txt']
        for file in file_lis:
            self.common.scp_file(self.primary_node, f"{file}", self.target_path)

        sql1 = f"""drop table if exists tab_ap_join_04;
        create table tab_ap_join_04(
        c_id  varchar(30) ,
         c_street_1  varchar(30) not null,
         c_city  varchar(2000) not null,
         c_zip  varchar(30) not null,
         c_d_id  numeric not null,
         c_w_id  integer not null,
         c_since timestamp,
         c_timestamptz varchar(2000),
         c_timestamp timestamp without time zone,
         c_interval varchar(30) not null,
         c_date     date not null
         ) 
        with (storage_type=ustore) partition by range (c_date,c_w_id)
        (
          partition tab_ap_join_04_1 values less than ('2017-03-31',5),
          partition tab_ap_join_04_2 values less than ('2017-07-31',450),
          partition tab_ap_join_04_3 values less than ('2017-09-30',1062),
          partition tab_ap_join_04_4 values less than ('2017-12-31',1765),
          partition tab_ap_join_04_5 values less than ('2018-03-31',2024),
          partition tab_ap_join_04_6 values less than ('2018-07-31',2384),
          partition tab_ap_join_04_7 values less than ('2018-09-30',2786),
          partition tab_ap_join_04_8 values less than (maxvalue,maxvalue)
        );
        
        copy tab_ap_join_04 from 
        '{os.path.join(self.target_path, 'tab_ap_join_04.txt')}' delimiters  ',';
        
        create index tab_ap_join_04_1 on tab_ap_join_04(c_d_id,c_id)local; 
        create index tab_ap_join_04_2 on tab_ap_join_04(c_city)local;"""

        res1 = self.pricomsh.execut_db_sql(sql1)
        self.log.info(res1)

        self.assertIn('CREATE TABLE', res1, 'sql1执行失败')
        self.assertIn('COPY 3000', res1, 'sql1执行失败')
        self.assertIn('CREATE INDEX', res1, 'sql1执行失败')

    def tearDown(self):
        text = f"----step2:清理环境:expect:成功;----"
        self.log.info(text)
        del_flag = False
        del_cmd = f'rm -rf {self.target_path};' \
                  f'if [ -d {self.target_path} ]; ' \
                  f'then echo "exists";else echo "not exists"; fi'
        for num in range(10):
            del_res = self.common.get_sh_result(self.primary_node, del_cmd)
            if 'not exists' in del_res:
                del_flag = True
                break
        self.assertTrue(del_flag, '文件删除失败')
        self.log.info(f'----{os.path.basename(__file__)} finish----')
